set doc off
set verify off
--
--    Program: CAPGLEXT_SECURITY.sql
--    Description:
--    Extract security rules for all key segments associated with the sob that the user specified 
--
--    Revision:
--    1st     
--    Parameter: 1. set of books short name e.g. SOB_C999


--whenever sqlerror exit failure rollback
set serveroutput on size 1000000;

declare   
  l_count				   number;
  l_processed_count_r	number;
  l_rejected_count_r	   number;
  l_processed_count_l	number;
  l_rejected_count_l	   number;
  l_processed_count_u	number;
  l_rejected_count_u	   number;
  l_err_msg       		varchar2(2000);
  
  l_utl_file_1  	   utl_file.file_type;
  l_utl_file_2 		utl_file.file_type;
  l_utl_file_3 		utl_file.file_type;
  l_filename_1 		varchar2(255);
  l_filename_2 		varchar2(255);
  l_filename_3 		varchar2(255);
  l_pathname  		   varchar2(100);

  l_ngo_sob			   varchar2(20);

  cursor cur_s (p_ngo_sob varchar2) is
    select distinct flex_value_set_id
    from fnd_id_flex_segments where 
    id_flex_num = (select chart_of_accounts_id 
                   from gl_sets_of_books 
                   where upper(short_name) = upper(p_ngo_sob)); 
    
  cursor cur_r (p_set number) is
    select * from fnd_flex_value_rules 
    where flex_value_set_id = p_set;
    
  cursor cur_l (p_set number, p_rule number) is
    select * from fnd_flex_value_rule_lines 
    where flex_value_set_id = p_set
    and flex_value_rule_id = p_rule;
    
  cursor cur_u (p_set number, p_rule number) is
    select * from fnd_flex_value_rule_usages
    where flex_value_set_id = p_set
    and flex_value_rule_id = p_rule;  

begin
  l_pathname := '/tmp';
  l_filename_1 := 'CAPGL_SECURITY_RULE.dat';
  l_filename_2 := 'CAPGL_SECURITY_LINE.dat';
  l_filename_3 := 'CAPGL_SECURITY_USAGE.dat';
  
  l_utl_file_1 := utl_file.fopen(l_pathname, l_filename_1, 'w', 32767);
  l_utl_file_2 := utl_file.fopen(l_pathname, l_filename_2, 'w', 32767);
  l_utl_file_3 := utl_file.fopen(l_pathname, l_filename_3, 'w', 32767);
  
  l_count := 0;
  l_processed_count_r := 0;
  l_rejected_count_r  := 0;
  l_processed_count_l := 0;
  l_rejected_count_l  := 0;
  l_processed_count_u := 0;
  l_rejected_count_u  := 0;
                                           
  l_ngo_sob := '&1';
  
  for s in cur_s(l_ngo_sob) loop
    
    for r in cur_r (s.flex_value_set_id) loop    
      utl_file.put_line( l_utl_file_1, 
        r.flex_value_rule_id|| chr(9) ||
        r.flex_value_rule_name|| chr(9) ||          
        r.flex_value_set_id|| chr(9) ||
        to_char(r.last_update_date, 'DD-MON-YYYY HH24:MI:SS')|| chr(9) ||
        r.last_updated_by|| chr(9) ||
        to_char(r.creation_date, 'DD-MON-YYYY HH24:MI:SS')|| chr(9) ||
        r.created_by|| chr(9) ||
        r.last_update_login|| chr(9) ||
        r.parent_flex_value_low|| chr(9) ||
        r.parent_flex_value_high|| chr(9) ||
        CAPBREXT_COMMON.GetUser(r.last_updated_by)|| chr(9) ||
        CAPBREXT_COMMON.GetUser(r.created_by)
      );
    
      l_processed_count_r := l_processed_count_r + 1;      
  
      for l in cur_l(s.flex_value_set_id, r.flex_value_rule_id) loop        
        utl_file.put_line( l_utl_file_2, 
          l.flex_value_rule_id|| chr(9) ||
          l.flex_value_set_id|| chr(9) ||
          to_char(l.last_update_date, 'DD-MON-YYYY HH24:MI:SS')|| chr(9) ||
          l.last_updated_by|| chr(9) ||
          to_char(l.creation_date, 'DD-MON-YYYY HH24:MI:SS')|| chr(9) ||
          l.created_by|| chr(9) ||
          l.last_update_login|| chr(9) ||
          l.include_exclude_indicator|| chr(9) ||
          l.flex_value_low|| chr(9) ||
          l.flex_value_high|| chr(9) ||
          l.parent_flex_value_low|| chr(9) ||
          l.parent_flex_value_high|| chr(9) ||
          CAPBREXT_COMMON.GetUser(l.last_updated_by)|| chr(9) ||
          CAPBREXT_COMMON.GetUser(l.created_by)
        );
        
        l_processed_count_l := l_processed_count_l + 1;
      end loop;    

      for u in cur_u(s.flex_value_set_id, r.flex_value_rule_id) loop        
        utl_file.put_line( l_utl_file_3, 
          u.application_id|| chr(9) ||
          u.responsibility_id|| chr(9) ||
          u.flex_value_rule_id|| chr(9) ||
          to_char(u.last_update_date, 'DD-MON-YYYY HH24:MI:SS')|| chr(9) ||
          u.last_updated_by|| chr(9) ||
          to_char(u.creation_date, 'DD-MON-YYYY HH24:MI:SS')|| chr(9) ||
          u.created_by|| chr(9) ||
          u.last_update_login|| chr(9) ||
          u.flex_value_set_id|| chr(9) ||
          u.parent_flex_value_low|| chr(9) || 
          u.parent_flex_value_high|| chr(9) ||
          CAPBREXT_COMMON.GetUser(u.last_updated_by)|| chr(9) ||
          CAPBREXT_COMMON.GetUser(u.created_by)|| chr(9) ||
          CAPBREXT_COMMON.GetRespKey(u.responsibility_id)
        );
        
        l_processed_count_u := l_processed_count_u + 1;
      end loop;    

    end loop; 
                       
  end loop;
    
  l_err_msg := '';  
  if l_processed_count_r = 0 then
    l_rejected_count_r := l_rejected_count_r + 1;
    l_err_msg := 'No security rules found associated with the SOB '||l_ngo_sob;
  end if;    
    
  insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
  values ( 'CAPGLEXT_SECURITY', sysdate, l_processed_count_r, l_rejected_count_r, l_processed_count_r-l_rejected_count_r, l_err_msg );    
    
  l_err_msg := '';
  if l_processed_count_l = 0 then
    l_rejected_count_l := l_rejected_count_l + 1;
    l_err_msg := 'No security rule lines found associated with the SOB '||l_ngo_sob;
  end if;
        
  insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
  values ( 'CAPGLEXT_SECURITY', sysdate, l_processed_count_l, l_rejected_count_l, l_processed_count_l-l_rejected_count_l, l_err_msg );
  
  l_err_msg := '';
  if l_processed_count_u = 0 then
    l_rejected_count_u := l_rejected_count_u + 1;
    l_err_msg := 'No security rule usages found associated with the SOB '||l_ngo_sob;
  end if;
        
  insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
  values ( 'CAPGLEXT_SECURITY', sysdate, l_processed_count_u, l_rejected_count_u, l_processed_count_u-l_rejected_count_u, l_err_msg );
                 
  commit;
  
  utl_file.fclose(l_utl_file_1);
  utl_file.fclose(l_utl_file_2);
  utl_file.fclose(l_utl_file_3);
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;
end;
/

--exit
